# coding: utf-8

# 更新短信营销用户回应时间

import os
import set_env_path     # 请勿删除


from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def update_user_respond_datetime():
    """
    add by zhouhl 2017-10-27
    """

    select_respond_user_sql = text("""
        select id,mobile from user where sms_marketing_respond = True;
    """)

    find_exists_sms_order_sql = text("""
        select transfer_time from sms_orders where sender=:mobile order by sender desc;
    """)

    find_exists_call_order_sql = text("""
        select begin_time from call_order_for_lt where caller=:mobile order by begin_time desc;
    """)

    update_user_respond_datetime_sql = text("""
        update user set sms_marketing_respond_datetime = :respond_datetime where id = :id;
    """)

    records = conn.execute(select_respond_user_sql).fetchall()

    for i in records:
        respond_datetime = None

        tmp_sms_result = conn.execute(
            find_exists_sms_order_sql,
            mobile=i.mobile
        ).fetchone()
        if tmp_sms_result:
            respond_datetime = tmp_sms_result.transfer_time
        else:
            tmp_call_result = conn.execute(
                find_exists_call_order_sql,
                mobile=i.mobile
            ).fetchone()
            if tmp_call_result:
                respond_datetime = tmp_call_result.begin_time

        if respond_datetime:
            conn.execute(
                update_user_respond_datetime_sql,
                respond_datetime=respond_datetime,
                id=i.id,
            )


if __name__ == '__main__':
    update_user_respond_datetime()
